﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Core.Entity;
using System.Data;

namespace Core.Dao
{
    class UserDao
    {
        public bool insert(User user)
        {
            object[,] param = {
                {"@UserTypeId", user.UserTypeId},
                {"@Username", user.Username},
                {"@Password", user.Password},
                {"@Email", user.Email},
                {"@IsActive", user.IsActive},
            };
            String sql = "insert into USERS(UST_ID, USR_USERNAME, USR_PASSWORD, USR_EMAIL, USR_IS_ACTIVE) " +
                "values(@UserTypeId, @Username, @Password, @Email, @IsActive)";
            return DB.exec(sql, param);
        }

        public bool update(User user)
        {
            object[,] param = {
                {"@UserTypeId", user.UserTypeId},
                {"@Username", user.Username},
                {"@Password", user.Password},
                {"@Email", user.Email},
                {"@IsActive", user.IsActive},
                {"@id", user.Id},
            };
            String sql = "update USERS set UST_ID = @UserTypeId, USR_USERNAME = @Username, USR_PASSWORD = @Password, USR_EMAIL = @Email, USR_IS_ACTIVE = @IsActive " + 
                "where USR_ID = @id";
            return DB.exec(sql, param);
        }

        public bool delete(int id)
        {
            object[,] param = {
                {"@id", id}   
            };
            String sql = "delete from USERS where USR_ID = @id";
            return DB.exec(sql, param);
        }

        public bool delete(String ids)
        {
            object[,] param = {
                {"@ids", ids}   
            };
            String sql = "delete from USERS where USR_ID in (" + ids + ")";
            return DB.exec(sql);
        }

        public DataTable get(int id)
        {
            object[,] param = {
                {"@id", id}   
            };
            String sql = "select * from USERS where USR_ID = @id";
            return DB.getData(sql, param);
        }

        public DataTable getAll()
        {
            String sql = "select * from USERS order by USR_USERNAME";
            return DB.getData(sql);
        }

        public DataTable login(string login, string password)
        {
            object[,] param = {
                {"@Username", login},
                {"@Email", login},
                {"@Password", password}   
            };
            String sql = "select * from USERS where (USR_USERNAME = @Username or USR_EMAIL = @Email) and USR_PASSWORD = @Password and USR_IS_ACTIVE = 1";
            return DB.getData(sql, param);
        }

        public object getInsertedId()
        {
            String sql = "select IDENT_CURRENT('USERS')";
            return DB.getValue(sql);
        }

        internal DataTable getByUsername(string username)
        {
            object[,] param = {
                {"@username", username}   
            };
            String sql = "select * from USERS where USR_USERNAME = @username";
            return DB.getData(sql, param);
        }

        internal DataTable getByEmail(string email)
        {
            object[,] param = {
                {"@email", email}   
            };
            String sql = "select * from USERS where USR_EMAIL = @email";
            return DB.getData(sql, param);
        }
    }
}
